package com.wujunhao1024.sqldp;


import com.wujunhao1024.sqldp.render.RenderUtil;

public class Main {

    public static void main(String[] args) throws Exception {
//         String sql = "SELECT t1.a + t1.b AS sum_ab, t1.c FROM table_1 t1  WHERE t1.d > 10 and t1.e < 20";
//         String sql = "select sum(t1.a) from table_1 t1 group by t1.c";
//        String sql = "select concat(sum(t1.a + t1.b * 2 - t1.c * 3), t1.d) as test from table_1 t1 group by t1.e";
//        String sql = "select concat(sum(t1.a + t1.b * 2 - t1.c * 3), t1.d) as test from (select table_a a, table_b b, table_c c, table_d d, table_e e from table_t1) as t1 group by t1.e";
//        String sql = "select concat(sum(t1.a + t1.b * 2 - t1.c * 3), t1.d) as test from (select t2.table_a a, t2.table_b b, t2.table_c c, t2.table_d d, t2.table_e e from table_t1 t2) as t1 group by t1.e";
        String sql = "select concat(sum(t1.a + t1.b * 2 - t1.c * 3), t2.a) as test from table_1 t1 left join table_2 t2 on t1.id = t2.id group by t1.e";


//        String sql = "select a.*\n" +
//                "from (\n" +
//                "        select\n" +
//                "            a.cert_no\n" +
//                "            ,min(pre_arrive_and_pre_departure_time) as first_arrive_time\n" +
//                "        from (\n" +
//                "            select\n" +
//                "                 rep_key\n" +
//                "                ,id_card_no as cert_no\n" +
//                "            from dwd_ma_opt_ship_entry_exit_crew_report_df_buji  \n" +
//                "            where dt = max_pt('dwd_ma_opt_ship_entry_exit_crew_report_df_buji')\n" +
//                "            and   crew_duty_name = '船长'\n" +
//                "            group by rep_key,id_card_no\n" +
//                "        ) a \n" +
//                "        inner join dwd_ma_opt_report_detail_info_df_buji b \n" +
//                "        on  a.rep_key = b.rep_key\n" +
//                "        and b.dt = max_pt('dwd_ma_opt_report_detail_info_df_buji')\n" +
//                "        and b.is_revoked = '0'\n" +
//                "        and b.reprot_org_code like '07%'\n" +
//                "        and b.pre_arrive_and_pre_departure_time >= '2024-06-01 00:00:00'\n" +
//                "        and b.pre_arrive_and_pre_departure_time <  cast(current_date() as datetime)\n" +
//                "        group by a.cert_no\n" +
//                ") a\n" +
//                "left join ods_captain_arrive_list b on a.cert_no = b.cert_no\n" +
//                "where b.cert_no is null";


//        String sql = "    select\n" +
//                "         a.ship_id\n" +
//                "        ,a.ship_no\n" +
//                "        ,a.idcard_no\n" +
//                "        ,a.crew_name\n" +
//                "        ,round(datediff(cast(current_date() as datetime), c.min_offi_date, 'dd') / 365,2) as ship_captain_office_duration\n" +
//                "    from ods_msagx_per_crew_servqual_df a      \n" +
//                "    left join ods_ythcy_t_dict_crewduties_a b  \n" +
//                "    on    a.offi_posi_code = b.comp_duties_code\n" +
//                "    left join (\n" +
//                "                select\n" +
//                "                     b.idcard_no\n" +
//                "                    ,min(b.offi_date) as min_offi_date\n" +
//                "                from ods_msagx_per_crew_servqual_df a      \n" +
//                "                left join ods_ythcy_t_dict_crewduties_a b  \n" +
//                "                on    a.offi_posi_code = b.comp_duties_code\n" +
//                "                where a.dt = max_pt('ods_msagx_per_crew_servqual_df')\n" +
//                "                and   a.if_valid_code = '1'\n" +
//                "                and   b.comp_duties_name = 'CZ'\n" +
//                "                group by idcard_no\n" +
//                "    ) c \n" +
//                "    on a.idcard_no = c.idcard_no\n" +
//                "    where a.dt = max_pt('ods_msagx_per_crew_servqual_df')\n" +
//                "    and   a.if_valid_code = '1'\n" +
//                "    and   a.offi_dism_flag = '1'\n" +
//                "    and   b.comp_duties_name = 'CZ'";

//        String sql = "SELECT a.idcard_no, min(a.offi_date) AS \n" +
//                "min_offi_date FROM ods_msagx_per_crew_servqual_df a LEFT JOIN \n" +
//                "ods_ythcy_t_dict_crewduties_a b ON \n" +
//                "a.offi_posi_code = b.comp_duties_code \n" +
//                "WHERE a.dt = max_pt('ods_msagx_per_crew_servqual_df') AND \n" +
//                "a.if_valid_code = '1' AND \n" +
//                "b.comp_duties_name = 'CZ' GROUP \n" +
//                "BY idcard_no";


//        String sql = "select \n" +
//                "        a.*,b.ship_union_no,1 as flag\n" +
//                "    from t1 a \n" +
//                "    left join dwd_ma_bas_vel_ship_info_df b \n" +
//                "    on  a.ship_id = b.ship_id \n" +
//                "    and b.dt = max_pt('dwd_ma_bas_vel_ship_info_df')\n" +
//                "    and b.status_flag = '1'\n" +
//                "    and b.ship_id is not null";


//        String sql = "    select distinct \n" +
//                "         ship_id\n" +
//                "        ,ship_no\n" +
//                "        ,idcard_no\n" +
//                "        ,crew_name\n" +
//                "    from ods_msagx_per_crew_servqual_df a     \n" +
//                "    left join ods_ythcy_t_dict_crewduties_a b  \n" +
//                "    on    a.offi_posi_code = b.comp_duties_code\n" +
//                "    where a.dt = max_pt('ods_msagx_per_crew_servqual_df')\n" +
//                "    and   a.if_valid_code = '1'\n" +
//                "    and   a.offi_dism_flag = '1'\n" +
//                "    and   b.comp_duties_name in ('CZ')";

//        String sql = "select \n" +
//                "      ship_union_no\n" +
//                "     ,count(distinct idcard_no) as ship_last_1y_kf_crew_num\n" +
//                "from (\n" +
//                "    select\n" +
//                "        *\n" +
//                "        ,row_number() over(partition by ship_id,ship_no,idcard_no order by flag asc) rn \n" +
//                "    from t2\n" +
//                "    where ship_union_no is not null\n" +
//                ") a\n" +
//                "where rn = 1\n" +
//                "group by ship_union_no\n" +
//                ";";
        RenderUtil.render(sql);
    }
}